﻿using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using XunChi.Log;

namespace XunChi.DBHelper
{
    /// <summary>
    /// 使用Oracle.ManagedDataAccess.dll，放弃微软自带的System.Data.OracleClient.dll，采用第三方标准的Oracle数据库提供程序。
    /// </summary>
    public static class OracleHelperNew
    {
        /// <summary>
        ///      为数据库执行准备命令的内部功能
        /// </summary>
        /// <param name="cmd">现有命令对象</param>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="trans">Oracle事务对象</param>
        /// <param name="cmdType">命令类型，例如存储过程</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="commandParameters">命令的参数</param>
        private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] commandParameters)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
              
            //设置命令
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            //如果存在，则将其绑定到交易
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
               
            // 绑定传入的参数
            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        ///  执行不包含选择的数据库查询
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="cmdType">命令类型存储过程或SQL</param>
        /// <param name="cmdText">执行的命令文本</param>
        /// <param name="commandParameters">要绑定到命令的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            //创建一个OracleCommand
            OracleCommand cmd = new OracleCommand();

            //创建连接
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                //准备命令行
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

                //执行命令行
                int val = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 根据SQL语句和传入的参数数组执行返回DataTable数据集。
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExcuteDataTable(string connectionString, string sql, params object[] parameters)
        {
            //创建一个OracleCommand
            OracleCommand cmd = new OracleCommand();

            using (OracleConnection con = new OracleConnection(connectionString))
            {
                con.Open();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;

                DataTable dt = new DataTable();
                //2.创建适配器

                OracleDataAdapter da = new OracleDataAdapter();
                da.SelectCommand = cmd;
                da.SelectCommand.Connection = con;
                List<OracleParameter> oracleParametersList = new List<OracleParameter>();
                //2.1设置查询命令的参数
                //for (int i = 0;i<parameters.Length; i++)
                //{
                //    OracleParameter oracleParameter = new OracleParameter();
                //    oracleParameter.ParameterName = "p" + i;
                //    oracleParameter.Value = parameters[i].ToString();
                //    oracleParametersList.Add(oracleParameter);
                //} 
                //或者for可以替换foreach成关键字
                int i2 = 0;

                if (parameters!=null && parameters.Length >0 )
                {
                    foreach (var parm in parameters)
                    {
                        OracleParameter oracleParameter = new OracleParameter();
                        oracleParameter.ParameterName = "p" + i2;
                        oracleParameter.Value = parm;
                        oracleParametersList.Add(oracleParameter);
                    }
                }

                if (oracleParametersList.Count > 0)
                {
                    da.SelectCommand.Parameters.AddRange(oracleParametersList.ToArray());
                }
                //4.将数据查询并填充到数据表中
                da.Fill(dt);
                con.Close();
                //打印对应的SQL语句
                SqlLogHelper.PrintSQL(cmd);
                return dt;
            }
        }

        /// <summary>
        /// 根据SQL语句和传入的参数数组执行返回DataTable数据集。
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable ExcuteDataTableByEF(this Database db, string sql, params object[] parameters)
        {
            if (db.Connection.State == ConnectionState.Closed)
            {
                db.Connection.Open();
            }
            DataTable dt = new DataTable();
            System.Data.Common.DbCommand cmd = db.Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = db.Connection;
            //2.创建适配器
            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = cmd as OracleCommand;
            List<OracleParameter> oracleParametersList = new List<OracleParameter>();
            //2.1设置查询命令的参数
            //for (int i = 0;i<parameters.Length; i++)
            //{
            //    OracleParameter oracleParameter = new OracleParameter();
            //    oracleParameter.ParameterName = "p" + i;
            //    oracleParameter.Value = parameters[i].ToString();
            //    oracleParametersList.Add(oracleParameter);
            //} 
            //或者for可以替换foreach成关键字
            int i2 = 0;
            foreach (var parm in parameters)
            {
                OracleParameter oracleParameter = new OracleParameter();
                oracleParameter.ParameterName = "p" + i2;
                oracleParameter.Value = parm;
                oracleParametersList.Add(oracleParameter);
            }

            da.SelectCommand.Parameters.AddRange(oracleParametersList.ToArray());
            //4.将数据查询并填充到数据表中
            da.Fill(dt);
            db.Connection.Close();
            //打印对应的SQL语句
            SqlLogHelper.PrintSQL(cmd);
            return dt;
        }

        /// <summary>
        /// 根据参数数组执行SQL语句返回DataTable数据集。
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>

        public static DataTable ExcuteDataTableByParam(this Database db, string sql, params OracleParameter[] parameters)
        {
            if (db.Connection.State == ConnectionState.Closed) { 
                db.Connection.Open(); 
            }
            DataTable dt = new DataTable();
            System.Data.Common.DbCommand cmd = db.Connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = db.Connection;
            //2.创建适配器
            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = cmd as OracleCommand;
            //2.1设置查询命令的参数
            da.SelectCommand.Parameters.AddRange(parameters);
            //4.将数据查询并填充到数据表中
            da.Fill(dt);
            db.Connection.Close();
            //打印对应的SQL语句
            SqlLogHelper.PrintSQL(cmd);
            return dt;
        }
    }
}
